package com.zhongge.dao.impl;

import com.zhongge.dao.StudentDao;
import com.zhongge.entity.Student;
import com.zhongge.util.DBUtils;

import java.sql.*;
import java.util.ArrayList;

/**
 * @ClassName StudentDaoImpl
 * @Description TODO
 * @Author zhongge
 * @Version 1.0
 */
public class StudentDaoImpl implements StudentDao {
    /**
     * 查询所有学生信息
     * @return 集合
     */
    @Override
    public ArrayList<Student> findAll() {
        ArrayList<Student> list = new ArrayList<>();
        //连接
        Connection conn = null;
        //数据库预操作对象
        PreparedStatement ps = null;
        //结果集
        ResultSet re = null;

        try {
            //获取连接
            conn = DBUtils.getConnection();

            //sql语句
            String sql = "select id, name, age, sex, birthday, address, remark from t_student";

            //预编译sql语句 ==> 将sql传入到 预操作对象中 返回一个预编译sql的对象
            ps = conn.prepareStatement(sql);

            //执行sql语句==》将sql传入到数据库中执行
            re = ps.executeQuery();

            //处理结果
            while (re.next()) {
                //创建学生对象
                Student student = new Student();
                //对学生进行初始化
                student.setId(re.getInt("id"));
                student.setName(re.getString("name"));
                student.setAge(re.getInt("age"));
                student.setSex(re.getInt("sex"));
                student.setBirthday(re.getDate("birthday"));
                student.setAddress(re.getString("address"));
                student.setRemark(re.getString("remark"));
                //将学生对象加入到集合中
                list.add(student);
            }
            return list;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBUtils.close(conn, ps, re);
        }
    }

    /**
     * 根据ID获取学生信息
     * @param id 学生id
     * @return 学生信息
     */
    @Override
    public Student getById(Integer id) {
        //连接
        Connection conn = null;
        //数据库预操作对象
        PreparedStatement ps = null;
        //结果集
        ResultSet re = null;
        //学生对象
        Student student = null;

        try {
            //获取连接
            conn = DBUtils.getConnection();

            //先写出含有张伟符号的sql语句（防止SQL注入）
            String sql = "select id, name, age, sex, birthday, address, remark from t_student where id = ?";

            //预编译sql语句 ==> 将sql传入到 预操作对象中 返回一个预编译sql的对象
            ps = conn.prepareStatement(sql);

            //对占位符进行填充
            ps.setInt(1, id);

            //执行sql语句==》将sql传入到数据库中执行
            re = ps.executeQuery();

            //处理结果
            while (re.next()) {
                //创建学生对象
                student = new Student();
                //对学生进行初始化
                student.setId(re.getInt("id"));
                student.setName(re.getString("name"));
                student.setAge(re.getInt("age"));
                student.setSex(re.getInt("sex"));
                student.setBirthday(re.getDate("birthday"));
                student.setAddress(re.getString("address"));
                student.setRemark(re.getString("remark"));
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBUtils.close(conn, ps, re);
        }
        return student;
    }

    /**
     * 添加学生
     * @param student 学生
     * @return true 添加成功  false 添加失败
     */
    @Override
    public boolean addStudent(Student student) {
        //连接
        Connection conn = null;
        //数据库预操作对象
        PreparedStatement ps = null;

        try {
            //获取连接
            conn = DBUtils.getConnection();

            //先写出含有张伟符号的sql语句（防止SQL注入）
            String sql = "insert into t_student(name, age, sex, birthday, address, remark ) values(?, ?, ?, ?, ?, ?)";

            //预编译sql语句 ==> 将sql传入到 预操作对象中 返回一个预编译sql的对象
            ps = conn.prepareStatement(sql);

            //对占位符进行填充
            ps.setString(1, student.getName());
            ps.setInt(2, student.getAge());
            ps.setInt(3, student.getSex());
            //student.getBirthday().getTime()==>查资料得到
            ps.setDate(4, new Date(student.getBirthday().getTime()));
            ps.setString(5, student.getAddress());
            ps.setString(6, student.getRemark());

            //执行sql语句==》将sql传入到数据库中执行
            int insertResult = ps.executeUpdate();
            if (insertResult > 0) {
                return true;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(conn, ps, null);
        }
        return false;
    }

    /**
     * 修改信息
     * @param student
     * @return
     */
    @Override
    public boolean updateById(Student student) {
        //连接
        Connection conn = null;
        //数据库预操作对象
        PreparedStatement ps = null;

        try {
            //获取连接
            conn = DBUtils.getConnection();

            //先写出含有张伟符号的sql语句（防止SQL注入）
            String sql = "update t_student set name = ?, age = ?, sex = ?, birthday = ?, address = ?, remark = ?  where id = ?";

            //预编译sql语句 ==> 将sql传入到 预操作对象中 返回一个预编译sql的对象
            ps = conn.prepareStatement(sql);

            //对占位符进行填充
            ps.setString(1, student.getName());
            ps.setInt(2, student.getAge());
            ps.setInt(3, student.getSex());
            //student.getBirthday().getTime()==>查资料得到
            ps.setDate(4, new Date(student.getBirthday().getTime()));
            ps.setString(5, student.getAddress());
            ps.setString(6, student.getRemark());
            ps.setInt(7, student.getId());

            //执行sql语句==》将sql传入到数据库中执行
            int insertResult = ps.executeUpdate();
            if (insertResult > 0) {
                return true;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(conn, ps, null);
        }
        return false;
    }

    /**
     * 删除信息
     * @param id
     * @return
     */
    @Override
    public boolean deleteById(Integer id) {
        //连接
        Connection conn = null;
        //数据库预操作对象
        PreparedStatement ps = null;

        try {
            //获取连接
            conn = DBUtils.getConnection();

            //先写出含有张伟符号的sql语句（防止SQL注入）
            String sql = "delete from t_student where id = ?";

            //预编译sql语句 ==> 将sql传入到 预操作对象中 返回一个预编译sql的对象
            ps = conn.prepareStatement(sql);

            //对占位符进行填充
            ps.setInt(1, id);

            //执行sql语句==》将sql传入到数据库中执行
            int insertResult = ps.executeUpdate();
            if (insertResult > 0) {
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(conn, ps, null);
        }
        return false;
    }
}
